package dao;

import java.sql.*;
import java.util.LinkedList;
import domain.Movie;
import domain.User;
import ex.DatabaseException;
import util.*;

public class MovieDAO {

	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;

	private static final String SELECT_MOVIE_BY_MID = "select * from movie where mid = ? ";
	private static final String SELECT_MOVIE_BY_UID = "select mid from movie where uid=?";
	private static final String CREATE_MOVIE = "insert into movie (name,genre,year,runTime,summary,poster,linkToMovie,rating,popularity,isCompleted,uid) values (?, ?, ?, ?, ? ,?, ?, ?, ?, ?,?) ";
	private static final String SELECT_DIRECTORS_BY_MID = "select uid from directed where mid= ? ";
	private static final String SELECT_ACTORS_BY_MID = "select uid from acted where mid= ? ";
	private static final String SELECT_CREW_BY_MID = "select uid from worked_as_crew where mid = ? ";
	private static final String UPDATE_MOVIE = "update movie set name = ?, genre = ?, year = ?, summary = ?, runTime = ?, poster = ?, +"
			+ " linkToMovie = ?, rating = ?, popularity = ?, isCompleted = ?, timestamp = ? where mid= ? ";
	private static final String DELETE_MOVIE = "DELETE FROM movie WHERE mid = ? ";
	private static final String TOP_MOVIE = "SELECT * FROM movie WHERE isCompleted = 1 ORDER BY rating DESC LIMIT ?";
	private static final String LATEST_MOVIES = "Select Top n mid from movie WHERE isCompleted = 1 Order BY timestamp where n = ?";
	private static final String LAST_MOVIE = "Select * from movie Order by mid desc limit 1";

	// Selection of the list of the movies created by a user
	public LinkedList<Movie> selectMovieByUid(long uid)
			throws DatabaseException {
		conn = JDBCUtil.getConnection();
		LinkedList<Movie> movielist = new LinkedList<Movie>();
		try {
			stmt = conn.prepareStatement(SELECT_MOVIE_BY_UID);
			stmt.setLong(1, uid);
			rs = stmt.executeQuery();
			while (rs.next()) {
				movielist.add(selectMovieByMid(rs.getLong("mid")));
			}

			stmt.close();
			JDBCUtil.closeConnection(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving movie information by uid! "
							+ e.getMessage());
		}
		return movielist;
	}

	// selecting the information of a movie using Movie ID
	public Movie selectMovieByMid(long mid) throws DatabaseException {
		conn = JDBCUtil.getConnection();
		long uid=-1;
		String name = null;
		String genre = null;
		String year = null;
		String summary = null;
		int runTime = -1;
		String poster = null;
		String linkToMovie = null;
		double rating = -1;
		int popularity = -1;
		int isCompleted = -1;
		LinkedList<User> actedList = null;
		LinkedList<User> directedList = null;
		LinkedList<User> crewList = null;
		Timestamp timestamp = null;
		Movie movie = null;
		UserDAO udao = new UserDAO();
		try {
			stmt = conn.prepareStatement(SELECT_MOVIE_BY_MID);
			stmt.setLong(1, mid);
			rs = stmt.executeQuery();
			if (rs.next()) {
				uid=rs.getLong("uid");
				name = rs.getString("name");
				genre = rs.getString("genre");
				year = rs.getString("year");
				summary = rs.getString("summary");
				runTime = rs.getInt("runTime");
				poster = rs.getString("poster");
				linkToMovie = rs.getString("linkToMovie");
				rating = rs.getDouble("rating");
				popularity = rs.getInt("popularity");
				isCompleted = rs.getInt("isCompleted");
				timestamp = rs.getTimestamp("timestamp");
			}
			stmt.close();
			// selecting directors of the movie
			
			stmt = conn.prepareStatement(SELECT_DIRECTORS_BY_MID);
			stmt.setLong(1, mid);
			rs = stmt.executeQuery();
			directedList = new LinkedList<User>();
			while (rs.next()) {
				directedList.add(udao.selectUserByUid(rs.getLong("uid")));
			}
			stmt.close();
			// selecting actors of the movie
			stmt = conn.prepareStatement(SELECT_ACTORS_BY_MID);
			stmt.setLong(1, mid);
			rs = stmt.executeQuery();
			actedList = new LinkedList<User>();
			while (rs.next()) {
				actedList.add(udao.selectUserByUid(rs.getLong("uid")));
			}
			stmt.close();
			// selecting crews of the movie
			stmt = conn.prepareStatement(SELECT_CREW_BY_MID);
			stmt.setLong(1, mid);
			rs = stmt.executeQuery();
			crewList = new LinkedList<User>();
			while (rs.next()) {
				crewList.add(udao.selectUserByUid(rs.getLong("uid")));
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving movie information by mid! "
							+ e.getMessage());
		}
		User u=udao.selectUserByUid(uid);
		movie = new Movie(mid,u ,name, genre, year, summary, runTime, poster,
				linkToMovie, rating, popularity, isCompleted == 1,
				directedList, actedList, crewList, timestamp);
		return movie;
	}

	// Insertion of a new Movie to the database
	public void insertMovie(Movie movie) throws DatabaseException {
		conn = JDBCUtil.getConnection();

		try {
			stmt = conn.prepareStatement(CREATE_MOVIE);
			stmt.setString(1, movie.getName());
			stmt.setString(2, movie.getGenre());
			stmt.setString(3, movie.getYear());
			stmt.setInt(4, movie.getRunTime());
			stmt.setString(5, movie.getSummary());
			stmt.setString(6, movie.getPoster());
			stmt.setString(7, movie.getLinkToMovie());
			stmt.setDouble(8, movie.getRating());
			stmt.setInt(9, movie.getPopularity());
			stmt.setInt(10, movie.getIsCompleted() ? 0 : 1);
			stmt.setLong(11, movie.getUser()!= null?movie.getUser().getUid() : -1);

			stmt.execute();

			stmt.close();
			JDBCUtil.closeConnection(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with inserting movie information! "
							+ e.getMessage());
		}
	}

	// Editing the Movie fields
	public void updateMovie(Movie mv) throws DatabaseException {
		conn = JDBCUtil.getConnection();

		try {
			stmt = conn.prepareStatement(UPDATE_MOVIE);
			stmt.setString(1, mv.getName());
			stmt.setString(2, mv.getGenre());
			stmt.setString(3, mv.getYear());
			stmt.setString(4, mv.getSummary());
			stmt.setInt(5, mv.getRunTime());
			stmt.setString(6, mv.getPoster());
			stmt.setString(7, mv.getLinkToMovie());
			stmt.setDouble(8, mv.getRating());
			stmt.setInt(9, mv.getPopularity());
			int i = 0;
			if (mv.getIsCompleted())
				i = 1;
			stmt.setInt(10, i);
			stmt.setTimestamp(11, mv.getTimestamp());
			stmt.setLong(12, mv.getMid());

			stmt.execute();

			stmt.close();
			JDBCUtil.closeConnection(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with editing movie information! " + e.getMessage());
		}
	}

	// Removal of a movie from the database
	public void deleteMovie(Movie mv) throws DatabaseException {
		conn = JDBCUtil.getConnection();

		try {
			stmt = conn.prepareStatement(DELETE_MOVIE);
			stmt.setLong(1, mv.getMid());

			stmt.execute();
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with deleting movie information! "
							+ e.getMessage());
		}
	}

	// Selection of the top movies
	public LinkedList<Movie> selectTopMovies(int n) throws DatabaseException {
		LinkedList<Movie> topMovies = new LinkedList<Movie>();
		Connection conn = JDBCUtil.getConnection();

		Movie movie = null;
		try {
			PreparedStatement stmt = conn.prepareStatement(TOP_MOVIE);
			stmt.setInt(1, n);

			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				long mid = rs.getLong("mid");
				movie = selectMovieByMid(mid);
				topMovies.add(movie);
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving top movies! "
					+ e.getMessage());
		}
		return topMovies;
	}

	// Selection of the latest movies
	public LinkedList<Movie> selectLatestMovies(int n) throws DatabaseException {
		LinkedList<Movie> latestMovies = new LinkedList<Movie>();
		Connection conn = JDBCUtil.getConnection();

		Movie movie = null;
		try {
			PreparedStatement stmt = conn.prepareStatement(LATEST_MOVIES);
			stmt.setInt(1, n);

			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				long mid = rs.getLong("mid");
				movie = selectMovieByMid(mid);
				latestMovies.add(movie);
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving latest movies! " + e.getMessage());
		}
		return latestMovies;
	}

	//Selects last added movie in the system
	public Movie selectLastMovie() throws DatabaseException {
		// lastMovie variable isn't used in anywhere TODO
		Movie lastMovie = null;
		Connection conn = JDBCUtil.getConnection();

		Movie movie = null;
		try {
			PreparedStatement stmt = conn.prepareStatement(LAST_MOVIE);

			ResultSet rs = stmt.executeQuery();
			if (rs.next()) {
				long mid = rs.getLong("mid");

				movie = selectMovieByMid(mid);
			}
			stmt.close();
			JDBCUtil.closeConnection(conn);
		} catch (SQLException e) {
			throw new DatabaseException(
					"Problem with retrieving latest movies! " + e.getMessage());
		}
		return movie;
	}

}